---
title: "Untitled"
output: html_document
date: "2023-04-14"
---
############################################
Title: 3_03_Figure_3.rmd
Purpose: This code creates Figure 3.
Last updated: 25-04-2023

Input:
    - Loan panel data. Latest version: "final_panel_loans.csv"
    - Bond panel data. Latest version: "final_panel_bonds.csv"
   
Output:
    - Figure 3

##########################################


0. Setup *Set your file path here*
```{r, results="hide", include=FALSE}
#Clear user defined environment
rm(list=ls())

#Load packages
require(stargazer); require(lubridate); library(sandwich); library(ggplot2); require(dplyr); require(tidyverse); library(readxl); library(sandwich); library(lmtest); 

#options
options(digits=5)
options(stringsAsFactors = FALSE)
options(scipen=999)

#Assign dplyr verbs  
select <- dplyr::select
rename <- dplyr::rename
mutate <- dplyr::mutate
filter <- dplyr::filter
arrange <- dplyr::arrange
distinct <- dplyr::distinct
group_by <- dplyr::group_by
summarise <- dplyr::summarise
list <- base::list

#Define directories
global <- ".../2024_RFS_Replication_TOSUBMIT"

```




1. Plot distribution of firm size in loan market (loan-month observations) 
----------------------------------------------------------

1.1 Load LSTA data 
```{r, results="hide", include=FALSE}

LSTA_monthly_ytm <- read_csv(paste0(global, "/Data/final_panel_loans.csv", ""), 
    col_types = cols(...1 = col_skip(), date_m = col_date(format = "%Y-%m-%d")))

```

1.2 Plot Loan size Dist
```{r, results="hide", include=FALSE}
#Calculate distribution
compustat_size <- LSTA_monthly_ytm %>% 
  select(date_m, LIN, gvkey_comb, fyear, avg_at) %>% 
  mutate(size_bucket = ifelse( avg_at <= 1000 , 1000, avg_at )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 1000 & size_bucket <=2000 , 2000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 2000 & size_bucket <=4000 , 4000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 4000 & size_bucket <=6000 , 6000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 6000 & size_bucket <=8000 , 8000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 8000 & size_bucket <=10000 ,10000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 10000 & size_bucket <=12000 , 12000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 12000 & size_bucket <=14000 , 14000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 14000 & size_bucket <=16000 , 16000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 16000 & size_bucket <=18000 , 18000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 18000 & size_bucket <=20000 , 20000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 20000 , 25000, size_bucket ))  

#Calculate distribution
compustat_size %>% 
  group_by(size_bucket) %>% 
  summarise(count=n()) %>% 
  filter(!is.na(size_bucket)) %>% 
  mutate(total = sum(count)) %>% 
  mutate(proportion = round(count/total,3)) %>% 
  mutate(check=sum(proportion)) 


#Data for plot
positions = c("<$1bill","$1-2", "$2-4","$4-6","$6-8","$8-10","$10-12", "$12-14", "$14-16", "$16-18", "$18-20", ">$20bill")
rating_cat = c("<$1bill","$1-2", "$2-4","$4-6","$6-8","$8-10","$10-12", "$12-14", "$14-16", "$16-18", "$18-20", ">$20bill")
rating= c(0.180, 0.191, 0.206, 0.095, 0.067, 0.042, 0.034, 0.023, 0.018, 0.021, 0.013, 0.110)
out=data.frame(rating_cat,rating)


#Plot
ggplot(out)+
    geom_col(aes(x=rating_cat, y=rating), size = 1, color = "darkblue", fill = "white", position = "dodge") +
    ggtitle("Loan Market") +
    ylab("Fraction of LIN-Month obs") +
    xlab("Size") +
    scale_x_discrete(limits=positions)+
    theme_bw()+
   theme(strip.text.x = element_text(size=8),
          strip.text.y = element_text(size=8),
          strip.background = element_rect(colour="white", fill="white")) +
  theme(axis.text=element_text(size=8)) + 
  theme(axis.title.x = element_text(size = 8)) +
  theme(axis.title.y = element_text(size = 8)) +
  theme(plot.title = element_text(color="black", size=8, face = "bold"))+
  ylim(0, 0.75)


```



2. Plot distribution of firm size in bond market (bond-month observations) 
---------------------------------

2.1 Load Bond data
```{r, results="hide", include=FALSE}

#Load bond spreads
TRACE_monthly_ytm_1999_2020  <- read_csv(paste0(global, "/Data/final_panel_bonds.csv", ""), 
    col_types = cols(...1 = col_skip(), date_m = col_date(format = "%Y-%m-%d")))

```

2.2 Plot Bond Size Dist
```{r, results="hide", include=FALSE}

#Calculate distribution
compustat_size <- TRACE_monthly_ytm_1999_2020 %>% 
  select(date_m, cusip_id, gvkey_ccl, fyear, avg_at) %>% 
  mutate(size_bucket = ifelse( avg_at <= 1000 , 1000, avg_at )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 1000 & size_bucket <=2000 , 2000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 2000 & size_bucket <=4000 , 4000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 4000 & size_bucket <=6000 , 6000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 6000 & size_bucket <=8000 , 8000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 8000 & size_bucket <=10000 ,10000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 10000 & size_bucket <=12000 , 12000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 12000 & size_bucket <=14000 , 14000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 14000 & size_bucket <=16000 , 16000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 16000 & size_bucket <=18000 , 18000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 18000 & size_bucket <=20000 , 20000, size_bucket )) %>% 
  mutate(size_bucket = ifelse( size_bucket > 20000 , 25000, size_bucket ))  

#Calculate distribution
compustat_size %>% 
  group_by(size_bucket) %>% 
  summarise(count=n()) %>% 
  filter(!is.na(size_bucket)) %>% 
  mutate(total = sum(count)) %>% 
  mutate(proportion = round(count/total,3)) %>% 
  mutate(check=sum(proportion)) 


#Data for plot
positions = c("<$1bill","$1-2", "$2-4","$4-6","$6-8","$8-10","$10-12", "$12-14", "$14-16", "$16-18", "$18-20", ">$20bill")
rating_cat = c("<$1bill","$1-2", "$2-4","$4-6","$6-8","$8-10","$10-12", "$12-14", "$14-16", "$16-18", "$18-20", ">$20bill")
rating= c(0.019, 0.029, 0.061, 0.057, 0.051, 0.049, 0.034, 0.034, 0.037, 0.032, 0.024, 0.572 )
out=data.frame(rating_cat,rating)

#PLot
ggplot(out)+
    geom_col(aes(x=rating_cat, y=rating), size = 1, color = "darkblue", fill = "white", position = "dodge") +
    ggtitle("Bond Market") +
    ylab("Fraction of CUSIP-Month obs") +
    xlab("Size") +
    scale_x_discrete(limits=positions)+
    theme_bw()+
   theme(strip.text.x = element_text(size=8),
          strip.text.y = element_text(size=8),
          strip.background = element_rect(colour="white", fill="white")) +
  theme(axis.text=element_text(size=8)) + 
  theme(axis.title.x = element_text(size = 8)) +
  theme(axis.title.y = element_text(size = 8)) +
  theme(plot.title = element_text(color="black", size=8, face = "bold"))+
  ylim(0, 0.75)

```



3. Combine into Figure 3
---------------------------------

3.1 Figure 3
```{r, results="hide", include=FALSE}

#Data for plot
positions = c("<$1bill","$1-2", "$2-4","$4-6","$6-8","$8-10","$10-12", "$12-14", "$14-16", "$16-18", "$18-20", ">$20bill")
rating_cat = c("<$1bill","$1-2", "$2-4","$4-6","$6-8","$8-10","$10-12", "$12-14", "$14-16", "$16-18", "$18-20", ">$20bill")
rating= c(0.180, 0.191, 0.206, 0.095, 0.067, 0.042, 0.034, 0.023, 0.018, 0.021, 0.013, 0.110)
out_loan=data.frame(rating_cat,rating)
out_loan <- out_loan %>% 
  mutate(Market = "Loan")

#Data for plot
positions = c("<$1bill","$1-2", "$2-4","$4-6","$6-8","$8-10","$10-12", "$12-14", "$14-16", "$16-18", "$18-20", ">$20bill")
rating_cat = c("<$1bill","$1-2", "$2-4","$4-6","$6-8","$8-10","$10-12", "$12-14", "$14-16", "$16-18", "$18-20", ">$20bill")
rating= c(0.019, 0.029, 0.061, 0.057, 0.051, 0.049, 0.034, 0.034, 0.037, 0.032, 0.024, 0.572 )
out_bond=data.frame(rating_cat,rating)
out_bond <- out_bond %>% 
  mutate(Market = "Bond")

#Combine
out = rbind(out_bond, out_loan)

#Plot
ggplot(out)+
  geom_col(aes(x = rating_cat, y = rating, colour = Market), fill = "white", size = 1,  position = "dodge", alpha = 0.6) +
  scale_colour_manual(values=c("Red", "Dark Blue")) +
    ylab("Fraction of LIN-Month obs") +
    xlab("Size") +
    scale_x_discrete(limits=positions)+
    theme_bw()+
   theme(strip.text.x = element_text(size=8),
          strip.text.y = element_text(size=8),
          strip.background = element_rect(colour="white", fill="white")) +
  theme(axis.text=element_text(size=8)) + 
  theme(axis.title.x = element_text(size = 8)) +
  theme(axis.title.y = element_text(size = 8)) +
  theme(plot.title = element_text(color="black", size=8, face = "bold"))+
  ylim(0, 0.75)+ theme(legend.position="bottom")


```



